<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>The sqlite3 commnad line tool</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="SQLite, SQL, database, sqlite3, command line tool">
<meta name="description" content="In this chapter of the SQLite tutorial, we cover
the sqlite3 commnad line tool.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content2">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>The sqlite3 command line tool</h1>


<p>
In this part of the SQLite tutorial, we will cover the 
sqlite3 command line tool. 
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
The manual describes the sqlite3 as follows:
<b>sqlite3</b> is a terminal-based front-end to the SQLite library that can
evaluate queries interactively and display the results in multiple formats.  
sqlite3 can also be used within shell scripts and other applications to provide 
batch processing features. 
</p>


<h2>sqlite3 tool</h2>

<b>sqlite3</b> is a terminal based frontend to the SQLite library that can evaluate
queries interactively and display the results in multiple formats.
It can also be used within scripts.
</p>

<p>
On the terminal screen, we see the following prompt of the sqlite3 tool. 
</p>

<pre class="code">
$ sqlite3 test.db 
SQLite version 3.7.7 2011-06-23 19:49:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
</pre>

<p>
The <b>.help</b> is one of the meta commnads of the sqlite3 tool. It 
shows a list of them. The <b>.exit</b> and the <b>.quit</b> commands
exit the sqlite3 session. We can also use the Ctrl+Q key combination. 
The <b>.databases</b> command shows the attached databases. 
The <b>.tables</b> command lists the available tables.
</p>

<h2>Creating a database</h2>

<p>
The complete SQLite database is stored in a single cross-platform disk file. We use
the <b>sqlite3</b> command line tool to create a new database file. 
</p>

<pre class="code">
$ sqlite3 movies.db
</pre>

<p>
Here we create a new movies.db database. If the exists, it is opened. 
</p>


<h2>Basic sqlite3 meta commnads</h2>

<p>
Next we describe some of the meta commands of the sqlite3 tool. 
</p>

<pre class="code">
sqlite> .tables
Books         Customers     Log           Orders        Testing     
Cars          Friends       Names         Reservations
</pre>

<p>
The <b>.tables</b> commnad shows the available tables.
</p>

<pre class="code">
sqlite> SELECT * FROM Friends;
1|Jane|F
2|Thomas|M
3|Frank|M
4|Elisabeth|F
5|Mary|F
6|Lucy|F
7|Jack|M
</pre>

<p>
Here we get the output of a simple SELECT statement. By default, the output mode is
<b>line</b> and the separator is |. 
</p>

<pre class="code">
sqlite> .separator :
sqlite> SELECT * FROM Friends;
1:Jane:F
2:Thomas:M
3:Frank:M
4:Elisabeth:F
5:Mary:F
6:Lucy:F
7:Jack:M
</pre>

<p>
Here we have used a new colon separator. 
</p>

<p>
There are several other output modes available. The following example
will show the <b>column</b> output mode. 
</p>

<pre class="code">
sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM Friends;
Id          Name        Sex       
----------  ----------  ----------
1           Jane        F         
2           Thomas      M         
3           Frank       M         
4           Elisabeth   F         
5           Mary        F         
6           Lucy        F         
7           Jack        M  
</pre>

<p>
In this example, we have our data in the column mode. Plus we show the column headers with
the <b>.headers</b> command. By default, the headers are hidden. 
</p>

<p>
The <b>.width</b> command adjusts the size of the columns.
</p>

<pre class="code">
sqlite> SELECT Title, Author FROM Books;
Title          Author     
-------------  -----------
War and Peace  Leo Tolstoy
The Brothers   Fyodor Dost
Crime and Pun  Fyodor Dost
</pre>

<p>
Here, the column widths are not wide enough to display all data correctly.
</p>

<pre class="code">
sqlite> .width 22, 18
sqlite> SELECT Title, Author FROM Books;
Title                   Author            
----------------------  ------------------
War and Peace           Leo Tolstoy       
The Brothers Karamazov  Fyodor Dostoyevsky
Crime and Punishment    Fyodor Dostoyevsky
</pre>

<p>
Here we change the column widths. The first column
will be 22 characters wide, the second 18. 
</p>

<pre class="code">
sqlite> .show
     echo: off
  explain: off
  headers: on
     mode: column
nullvalue: ""
   output: stdout
separator: "|"
    stats: off
    width: 22 18 
</pre>

<p>
The <b>.show</b> command lists various settings. We can see the output mode, 
the separator used in the list mode or whether the headers are on. 
</p>

<pre class="code">
sqlite> .schema Cars
CREATE TABLE Cars(Id integer primary key, Name text, Cost integer);
</pre>

<p>
The <b>.schema</b> command shows the structure of the table. 
It gives the DDL SQL to create the table.
</p>

<h2>Executing SQL from the shell</h2>

<p>
We can execute SQL commands from the shell. 
</p>

<pre class="code">
$ sqlite3 test.db "SELECT * FROM Cars;"
1|Audi|52642
2|Mercedes|57127
3|Skoda|9000
4|Volvo|29000
5|Bentley|350000
6|Citroen|21000
7|Hummer|41400
8|Volkswagen|21600  
</pre>

<p>
Here we have non-interactively executed a SELECT SQL command. 
We selected all cars from the Cars table.
</p>


<h2>Dumping tables</h2>

<p>
It is possible to dump tables in SQL format to the disk. This way we can easily save
the structure and the data of a database table. 
</p>

<p>
We have the Cars table.
</p>

<pre class="code">
sqlite> SELECT * FROM Cars;
Id          Name        Cost      
----------  ----------  ----------
1           Audi        52642     
2           Mercedes    57127     
3           Skoda       9000      
4           Volvo       29000     
5           Bentley     350000    
6           Citroen     21000     
7           Hummer      41400     
8           Volkswagen  21600   
</pre>

<p>
Now, we are going to use the <b>.dump</b> command to 
dump the table.
<p>

<pre class="code">
sqlite> .dump Cars
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE Cars(Id integer primary key, Name text, Cost integer);
INSERT INTO "Cars" VALUES(1,'Audi',52642);
INSERT INTO "Cars" VALUES(2,'Mercedes',57127);
INSERT INTO "Cars" VALUES(3,'Skoda',9000);
INSERT INTO "Cars" VALUES(4,'Volvo',29000);
INSERT INTO "Cars" VALUES(5,'Bentley',350000);
INSERT INTO "Cars" VALUES(6,'Citroen',21000);
INSERT INTO "Cars" VALUES(7,'Hummer',41400);
INSERT INTO "Cars" VALUES(8,'Volkswagen',21600);
COMMIT;
</pre>

<p>
The <b>.dump</b> command shows us the SQL necessary to 
recreate the table.
</p>

<pre class="code">
sqlite> .output cars2.sql
sqlite> .dump Cars
</pre>

<p>
We can also redirect the output to a file. The <b>.output</b>
command will redirect the output to the cars2.sql file. 
</p>

<pre class="code">
$ cat cars2.sql 
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE Cars(Id integer primary key, Name text, Cost integer);
INSERT INTO "Cars" VALUES(1,'Audi',52642);
...
</pre>

<p>
We verify it.
</p>


<h2>Reading SQL</h2>

<p>
We can read SQL from a file name with the <b>.read</b> commnad.
</p>


<pre class="code">
sqlite> .tables Cars
Cars
sqlite> DROP TABLE CARS;
sqlite> .tables Cars
sqlite> .read cars.sql 
sqlite> .tables Cars
Cars
sqlite> SELECT * FROM Cars WHERE id=1;
Id          Name        Cost      
----------  ----------  ----------
1           Audi        52642     
</pre>

<p>
Here we have executed a series of commands. We drop the table and read it from the cars.sql.
</p>

<pre class="code">
$ tail -5 ~/.sqlite_history 
SELECT * FROM Cars LIMIT 2;
.help
.tables
.echo ON
SELECT * FROM Cars;
</pre>

<p>
The commands and statements are archived in the <i>.sqlite_history</i> file, located
in the home directory. Using the <code>tail</code> command, we show the last five
entries.
</p>


<h2>Resource file</h2>

<p>
The sqlite3 tool has a resource file called <b>.sqliterc</b>. It is located 
in the home directory. If there is no such file, we can simply create it.
We can place the meta commnads there or the regular SQL statements.
However, we should avoid using SQL in the resource file. 
</p>

<pre>
$ cat .sqliterc 
.mode column
.header on
.nullvalue NULL  
</pre>

<p>
Here is a simple example of a resource file. It has three meta commands. With resource file, 
we don't have to execute meta commnads all over again, when we start the sqlite3 tool. They will
be executed automatically at the start of the tool.
</p>

<pre>
$ sqlite3 test.db 
-- Loading resources from /home/janbodnar/.sqliterc
SQLite version 3.7.7 2011-06-23 19:49:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
</pre>

<p>
We have a message saying, that the tool loaded resources upon the beginning.
</p>


<h2>Command line options</h2>

<p>
The tool has several command line options. They mostly duplicate the meta commands.
Note, that commnad line options overwrite the resource file meta commands.
</p>

<pre class="code">
$ sqlite3 -help
Usage: sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
   -help                show this message
   -init filename       read/process named file
   -echo                print commands before execution
   -[no]header          turn headers on or off
   -bail                stop after hitting an error
   -interactive         force interactive I/O
   -batch               force batch I/O
   -column              set output mode to 'column'
   -csv                 set output mode to 'csv'
   -html                set output mode to HTML
   -line                set output mode to 'line'
   -list                set output mode to 'list'
   -separator 'x'       set output field separator (|)
   -stats               print memory stats before each finalize
   -nullvalue 'text'    set text string for NULL values
   -version             show SQLite version
   -vfs NAME            use NAME as the default VFS
</pre>

<p>
The -help options gives us the list of all available options with
a brief description.
</p>

<pre class="code">
$ sqlite3 -echo -line -noheader test.db 
SQLite version 3.7.7 2011-06-23 19:49:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT * FROM Cars LIMIT 2;
SELECT * FROM Cars LIMIT 2;
   Id = 1
 Name = Audi
 Cost = 52642

   Id = 2
 Name = Mercedes
 Cost = 57127
</pre>

<p>
We start the sqlite3 tool with -echo, -line and -noheader options. The SELECT command
is repeated/echoed after being launched. The ouput is in the line mode and we have
no headers. 
</p>


<pre class="code">
$ sqlite3 -version
-- Loading resources from /home/janbodnar/.sqliterc
3.7.7 2011-06-23 19:49:22 4374b7e83ea0a3fbc3691f9c0c936272862f32f
</pre>

<p>
We get the sqlite3 version.
</p>

<pre class="code">
$ sqlite3 -html test.db
-- Loading resources from /home/janbodnar/.sqliterc
SQLite version 3.7.7 2011-06-23 19:49:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite&gt; SELECT * FROM Cars LIMIT 2;
&lt;TR&gt;&lt;TH&gt;Id&lt;/TH&gt;&lt;TH&gt;Name&lt;/TH&gt;&lt;TH&gt;Cost&lt;/TH&gt;&lt;/TR&gt;
&lt;TR&gt;&lt;TD&gt;1&lt;/TD&gt;
&lt;TD&gt;Audi&lt;/TD&gt;
&lt;TD&gt;52642&lt;/TD&gt;
&lt;/TR&gt;
&lt;TR&gt;&lt;TD&gt;2&lt;/TD&gt;
&lt;TD&gt;Mercedes&lt;/TD&gt;
&lt;TD&gt;57127&lt;/TD&gt;
&lt;/TR&gt;
</pre>

<p>
The <b>-html</b> option causes the results to be output as simple HTML tables.
</p>

<p>
In this part of the SQLite tutorial, we worked with the sqlite3 command line tool. We have
described various meta commnads. We have shown, how to dump tables, read SQL from files;
we described the sqlite's resource file. 
</p>

<div class="center"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* horizontal */
google_ad_slot = "1734478269";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 
<br>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified January 7, 2013  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>


